PRAGMA FOREIGN_KEYS=0;
DROP TABLE IF EXISTS decoracii;
DROP TABLE IF EXISTS janr;
DROP TABLE IF EXISTS janr_spec;
DROP TABLE IF EXISTS prov_spec;
DROP TABLE IF EXISTS Roli;
DROP TABLE IF EXISTS sotrudniki;
DROP TABLE IF EXISTS spectacl;
DROP TABLE IF EXISTS spis_dec;
DROP TABLE IF EXISTS raspredelenie;

CREATE TABLE decoracii (inv_nom INTEGER PRIMARY KEY,
nasv_decor VARCHAR (30));

CREATE TABLE spectacl (nasv VARCHAR (30) PRIMARY KEY,
avtor VARCHAR (30),
year_post DATE CHECK (year_post BETWEEN datetime('0000-00-00') and datetime('now')),
kol_actov INTEGER,
cena_bil MONEY NOT NULL DEFAULT 0 CHECK (cena_bil>100));

CREATE TABLE janr (nasv_janr VARCHAR (30) PRIMARY KEY);

CREATE TABLE spis_dec (inv_nom_dec INTEGER REFERENCES decoracii (inv_nom),
nasv_spec VARCHAR (30) REFERENCES spectacl (nasv),
PRIMARY KEY (inv_nom_dec, nasv_spec));

CREATE TABLE janr_spec (nasv_janra VARCHAR (30),
nasv_sp VARCHAR (30),
PRIMARY KEY (nasv_janra, nasv_sp),
FOREIGN KEY (nasv_janra) REFERENCES janr (nasv_janr),
FOREIGN KEY (nasv_sp) REFERENCES spectacl (nasv));

CREATE TABLE prov_spec (n_spec VARCHAR (30) REFERENCES spectacl (nasv),
data_vr DATE,
tip VARCHAR (30),
PRIMARY KEY (n_spec, data_vr));

CREATE TABLE Roli (nas_spec VARCHAR (30) REFERENCES spectacl (nasv),
nasv_roli VARCHAR (30),
PRIMARY KEY (nas_spec, nasv_roli));

CREATE TABLE sotrudniki (pasport VARCHAR (30) PRIMARY KEY,
fio VARCHAR (30),
dolshnost VARCHAR(30),
tel_dom integer NOT NULL,
tel_mob integer NOT NULL);

create table raspredelenie (pasp VARCHAR (30) REFERENCES sotrudniki (pasport),
data_vremya DATE,
nasvanie_spec VARCHAR (30),
nas_roli VARCHAR (30),
PRIMARY KEY (pasp, data_vremya, nasvanie_spec, nas_roli),
FOREIGN KEY (data_vremya, nasvanie_spec) REFERENCES prov_spec (data_vr, n_spec),
FOREIGN KEY (nasvanie_spec, nas_roli) REFERENCES roli (nas_spec, nasv_roli));

PRAGMA FOREIGN_KEYS=1;

INSERT INTO decoracii values (123, 'penek');
INSERT INTO decoracii values (234, 'krovat');
INSERT INTO decoracii values (345, 'geliotina');
INSERT INTO spectacl values ('zachet', 'Aksenov', '2010-06-09', 4, 6500);
INSERT INTO spectacl values ('ekzamen', 'Grigorieva', '2009-06-04', 4, 1500);
INSERT INTO spectacl values ('kursovik', 'Kursanov', '2011-05-05', 6, 500);
INSERT INTO spis_dec values (345, 'zachet');
INSERT INTO spis_dec values (234, 'ekzamen');
INSERT INTO spis_dec values (123, 'kursovik');
INSERT INTO janr values ('dramma');
INSERT INTO janr values ('komediya');
INSERT INTO janr values ('tragediya');
INSERT INTO janr_spec values ('tragediya', 'zachet');
INSERT INTO janr_spec values ('dramma', 'ekzamen');
INSERT INTO janr_spec values ('komediya', 'kursovik');
INSERT INTO sotrudniki values ('4007 034256', 'Avadenko Anatoliy Arcadievich', 'actor', 125, 2345);
INSERT INTO sotrudniki values ('4003 555892', 'Acsenov Aleksey Vladimirovich', 'uborshik', 124, 1345);
INSERT INTO sotrudniki values ('3098 097846', 'Grigorieva Natalia Nikiforovna', 'director', 156, 6745);
INSERT INTO prov_spec values ('kursovik', '2012-05-05', 'Repeticia' );
INSERT INTO prov_spec values ('ekzamen', '2012-06-04', 'Spectacl' );
INSERT INTO prov_spec values ('zachet', '2012-06-09', 'Spectacl' );
INSERT INTO Roli values ('kursovik', 'Chudotvorec' );
INSERT INTO Roli values ('ekzamen', 'God' );
INSERT INTO Roli values ('zachet', 'Satana' );
INSERT INTO raspredelenie values ('4007 034256','2012-05-05', 'kursovik', 'Chudotvorec');
INSERT INTO raspredelenie values ('4007 034256','2012-06-04', 'ekzamen', 'God');
INSERT INTO raspredelenie values ('3098 097846','2012-06-09', 'zachet', 'Satana');
